package br.ufrj.neg.contatos.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import br.ufrj.neg.contatos.bean.ContatoBean;
import br.ufrj.neg.model.ServiceLocator;

public class ContatoModel {

	
	public int cadastrarContato(ContatoBean cb){
		Connection conn = ServiceLocator.getConnection();
		ResultSet rs = null;
		PreparedStatement ps = null;
		int itensAtualizados = 0;
		
		try{
			String sql = "INSERT INTO contatos(cont_ds_nome,cont_ds_email,cont_ds_endereco,cont_ds_cargo,cont_ds_telefone1,cont_ds_telefone2) VALUES(?,?,?,?,?,?)";
			
			ps = conn.prepareStatement(sql);
			ps.setString(1, cb.getNome());
			ps.setString(2, cb.getEmail());
			ps.setString(3, cb.getEndereco());
			ps.setString(4, cb.getCargo());
			ps.setString(5, cb.getTelefone1());
			ps.setString(6, cb.getTelefone2());
					
			itensAtualizados = ps.executeUpdate();			
			conn.commit();
			
			sql = "SELECT max(cont_cd_id) FROM contatos";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			
			rs.next();
			cb.setIdContato( rs.getInt(1) );

			sql = "INSERT INTO negociacao_contatos(nego_cd_id,cont_cd_id) VALUES(?,?)";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, cb.getIdNegociacao());
			ps.setInt(2, cb.getIdContato());
			
			ps.executeUpdate();
			conn.commit();
			
		}catch(SQLException ex){
			ex.printStackTrace();
		}finally{
			try {conn.close();} catch (SQLException e) {e.printStackTrace();}
			try {ps.close();} catch (SQLException e) {e.printStackTrace();}
		}
		
		return itensAtualizados;		
	}
	
	public int excluirContato(ContatoBean cb){
		Connection conn = ServiceLocator.getConnection();
		PreparedStatement ps = null;
		int itensAtualizados = 0;
		
		try{
			String sql = "DELETE FROM negociacao_contatos WHERE nego_cd_id=? and cont_cd_id=?";
			
			ps = conn.prepareStatement(sql);
			ps.setInt(1, cb.getIdNegociacao());
			ps.setInt(2, cb.getIdContato());
			
			itensAtualizados = ps.executeUpdate();
			conn.commit();
			
		}catch(SQLException ex){
			ex.printStackTrace();
		}finally{
			try {conn.close();} catch (SQLException e) {e.printStackTrace();}
			try {ps.close();} catch (SQLException e) {e.printStackTrace();}
		}
		
		return itensAtualizados;					
	}
	
	
	public ContatoBean getContato(int idContato){
		Connection conn = ServiceLocator.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		ContatoBean cb = new ContatoBean();
		
		try{
			String sql = "SELECT * FROM contatos WHERE cont_cd_id=?";
			
			ps = conn.prepareStatement(sql);
			ps.setInt(1, idContato);
			
			rs = ps.executeQuery();
			
			

			while(rs.next()){
				cb.setIdContato(rs.getInt("cont_cd_id"));
				cb.setCargo(rs.getString("cont_ds_cargo"));
				cb.setEmail(rs.getString("cont_ds_email"));
				cb.setEndereco(rs.getString("cont_ds_endereco"));
				cb.setNome(rs.getString("cont_ds_nome"));
				cb.setTelefone1(rs.getString("cont_ds_telefone1"));
				cb.setTelefone2(rs.getString("cont_ds_telefone2"));
			}
			
			
		}catch(SQLException ex){
			ex.printStackTrace();
		}finally{
			try {conn.close();} catch (SQLException e) {e.printStackTrace();}
			try {ps.close();} catch (SQLException e) {e.printStackTrace();}
		}
		
		return cb;		
	}
	
	public ArrayList<ContatoBean> getContatosPorNegociacao(int idNegociacao){
		Connection conn = ServiceLocator.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		ContatoBean cb;
		ArrayList<ContatoBean> contatos = new ArrayList<ContatoBean>();
		
		try{
			String sql = "SELECT c.* FROM negociacao_contatos nc "+
						 "LEFT JOIN contatos c ON nc.cont_cd_id = c.cont_cd_id " +
						 "WHERE nc.nego_cd_id =? ORDER BY c.cont_ds_nome ASC";
			
			ps = conn.prepareStatement(sql);
			ps.setInt(1, idNegociacao);

			rs = ps.executeQuery();

			while(rs.next()){
				cb = new ContatoBean();
				cb.setIdContato(rs.getInt("cont_cd_id"));
				cb.setCargo(rs.getString("cont_ds_cargo"));
				cb.setEmail(rs.getString("cont_ds_email"));
				cb.setEndereco(rs.getString("cont_ds_endereco"));
				cb.setNome(rs.getString("cont_ds_nome"));
				cb.setTelefone1(rs.getString("cont_ds_telefone1"));
				cb.setTelefone2(rs.getString("cont_ds_telefone2"));
				
				contatos.add(cb);
			}
			
			
		}catch(SQLException ex){
			ex.printStackTrace();
		}finally{
			try {conn.close();} catch (SQLException e) {e.printStackTrace();}
			try {ps.close();} catch (SQLException e) {e.printStackTrace();}
		}
		
		return contatos;		
	}		
	
	public int atualizarContato(ContatoBean cb){
		Connection conn = ServiceLocator.getConnection();
		PreparedStatement ps = null;
		int itensAtualizados = 0;
		
		try{
			String sql = "UPDATE contatos SET cont_ds_nome=?,cont_ds_email=?,cont_ds_endereco=?,cont_ds_cargo=?,cont_ds_telefone1=?,cont_ds_telefone2=? " +
						 " WHERE cont_cd_id = ?";
			
			ps = conn.prepareStatement(sql);
			ps.setString(1, cb.getNome());
			ps.setString(2, cb.getEmail());
			ps.setString(3, cb.getEndereco());
			ps.setString(4, cb.getCargo());
			ps.setString(5, cb.getTelefone1());
			ps.setString(6, cb.getTelefone2());
			ps.setInt(7, cb.getIdContato());
			
			itensAtualizados = ps.executeUpdate();
			conn.commit();
			
		}catch(SQLException ex){
			ex.printStackTrace();
		}finally{
			try {conn.close();} catch (SQLException e) {e.printStackTrace();}
			try {ps.close();} catch (SQLException e) {e.printStackTrace();}
		}
		
		return itensAtualizados;			
	}
	
}
